﻿
--生成 mediaroom 表和 关联表
USE [HKEA_CMS]
GO

/****** Object:  Table [dbo].[tblMediaRoom]    Script Date: 01/14/2011 10:07:55 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblMediaRoom](
	[MediaRoomUID] [int] IDENTITY(1,1) NOT NULL,
	[Category] [nvarchar](100) NULL,
	[CustomerUID] [int] NULL,
	[MediaType] [nvarchar](50) NULL,
	[AliasName] [nvarchar](120) NULL,
	[FileName] [nvarchar](255) NULL,
	[FileExt] [nvarchar](20) NULL,
	[FilePath] [nvarchar](Max) NULL,
	[FileAlt_Eng] [nvarchar](80) NULL,
	[FileAlt_Chi] [nvarchar](80) NULL,
	[FileLink_Chi] [nvarchar](255) NULL,
	[FileLink_Eng] [nvarchar](255) NULL,
	[Des_Eng] [text] NULL,
	[Des_Chi] [text] NULL,
	[IsPublish] [int] NULL,
	[LastModifyBy] [nvarchar](120) NULL,
	[CreatedAt] [datetime] NULL,
	[UpdatedAt] [datetime] NULL,
 CONSTRAINT [PK_tblMediaRoom] PRIMARY KEY CLUSTERED 
(
	[MediaRoomUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

-----------------------------------------------------

/****** Object:  Table [dbo].[tblMediaRoom_Related]    Script Date: 01/14/2011 10:11:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblMediaRoom_Related](
	[MediaRoomRelatedUID] [int] IDENTITY(1,1) NOT NULL,
	[MediaRoomUID] [int] NULL,
	--[Category] [nvarchar](255) NULL,
	[RelatedUID] [int] NULL,
	[IsPublish] [int] Default 1,
	[Deleted] [int] Default 0,
	[Created_At] [datetime] NULL,
	[Updated_At] [datetime] NULL,
 CONSTRAINT [PK_tblMediaRoom_Related] PRIMARY KEY CLUSTERED 
(
	[MediaRoomRelatedUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[tblMediaRoom_Related]  WITH CHECK ADD  CONSTRAINT [FK_tblMediaRoom_Related_tblMediaRoom] FOREIGN KEY([MediaRoomUID])
REFERENCES [dbo].[tblMediaRoom] ([MediaRoomUID])
GO

ALTER TABLE [dbo].[tblMediaRoom_Related] CHECK CONSTRAINT [FK_tblMediaRoom_Related_tblMediaRoom]
GO

---------------------------------------------------------------
---Customer 添加 新字段
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter TABLE dbo.tblCustomer ADD [ReferralBy] nvarchar(120) NULL;
alter TABLE dbo.tblCustomer ADD [ReferralDate] datetime NULL;
alter TABLE dbo.tblCustomer ADD [Intro_Eng] nvarchar(max) NULL;
alter TABLE dbo.tblCustomer ADD [Intro_Chi] nvarchar(max) NULL;
alter TABLE dbo.tblCustomer ADD [HKID_PassportNo] nvarchar(120) NULL;
alter TABLE dbo.tblCustomer ADD [CustomerSince] datetime NULL;
alter TABLE dbo.tblCustomer ADD [Deleted] int NULL Default 0;
UPDATE dbo.tblCustomer SET Deleted=0;

GO
--------------------------------

---mtblCustType 添加 新字段
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

alter TABLE dbo.mtblCustType ADD [Prefix] nvarchar(20) NULL;

UPDATE dbo.mtblCustType SET Prefix='HC' WHERE dbo.mtblCustType.CustTypeUID=1;
UPDATE dbo.mtblCustType SET Prefix='HI' WHERE dbo.mtblCustType.CustTypeUID=2;
UPDATE dbo.mtblCustType SET Prefix='FC' WHERE dbo.mtblCustType.CustTypeUID=3;
UPDATE dbo.mtblCustType SET Prefix='FI' WHERE dbo.mtblCustType.CustTypeUID=4;
UPDATE dbo.mtblCustType SET Prefix='AC' WHERE dbo.mtblCustType.CustTypeUID=5;
UPDATE dbo.mtblCustType SET Prefix='AI' WHERE dbo.mtblCustType.CustTypeUID=6;
UPDATE dbo.mtblCustType SET Prefix='NC' WHERE dbo.mtblCustType.CustTypeUID=7;
UPDATE dbo.mtblCustType SET Prefix='NI' WHERE dbo.mtblCustType.CustTypeUID=8;
UPDATE dbo.mtblCustType SET Prefix='HM' WHERE dbo.mtblCustType.CustTypeUID=9;

GO
-------------------------------------------------


--Address相关表新增字段--

alter table tblAddress add Department_Eng nvarchar(200) null
alter table tblAddress add  Department_Chi nvarchar(200) null
alter table stg_tblAddress add Department_Eng nvarchar(200) null
alter table stg_tblAddress add  Department_Chi nvarchar(200) null
alter table rej_tblAddress add Department_Eng nvarchar(200) null
alter table rej_tblAddress add  Department_Chi nvarchar(200) null
-----

---cust_branch--
  alter table tblcust_branch add SequenceNo int default 0 null 
 -----
 
 ----tblCust_ProdCat 表修改  把ProductCatUID字段修改成ProductSubCatUID； 增加一个ProductCatUID字段
sp_rename  'dbo.tblCust_ProdCat.ProductCatUID', 'ProductSubCatUID', 'column';
 alter TABLE dbo.tblCust_ProdCat ADD ProductCatUID int Not NULL default 0;

 ----tblCust_WebLogin修改
  alter TABLE dbo.tblCust_WebLogin ADD [STATUS] int Not NULL default 1;
  UPDATE dbo.tblCust_WebLogin SET [STATUS]=1;

---Brand数据转移牵涉到的SQL语句--

 USE [HKEA_CMS]
GO

/****** Object:  Table [dbo].[mtblBrand]    Script Date: 03/15/2011 15:53:37 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[mtblBrand](
	[BrandUID] [int] IDENTITY(1,1) NOT NULL,
	[CustomerUID] [int] null,
	[BrandName_Eng] [nvarchar](200) NULL,
	[BrandName_Chi] [nvarchar](200) NULL,
	[Intro_Eng] [nvarchar](max) NULL,
	[Intro_Chi] [nvarchar](max) NULL,
	[BrandLogo] [int] NULL,
	[LastModifyDate] [datetime] NOT NULL,
	[LastModifyBy] [nvarchar](50) NULL,
	[CreationDate] [datetime] NOT NULL,
 CONSTRAINT [PK_mtblBrand] PRIMARY KEY CLUSTERED 
(
	[BrandUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[mtblBrand] ADD  CONSTRAINT [DF_mtblBrand_LastModifyDate]  DEFAULT (getdate()) FOR [LastModifyDate]
GO

ALTER TABLE [dbo].[mtblBrand] ADD  CONSTRAINT [DF_mtblBrand_CreationDate]  DEFAULT (getdate()) FOR [CreationDate]
GO

--转移数据到临时表 --
  insert into stg_tblCust_Brand ([CustBrandUID]
      ,[CustomerUID]
      ,[BrandName_Eng]
      ,[BrandName_Chi]
      ,[ProductCatUID]
      ,[Deleted]
      ,[LastModifyDate]
      ,[LastModifyBy]
      ,[CreationDate])
      
  select 
  
  [CustBrandUID]
      ,[CustomerUID]
      ,[BrandName_Eng]
      ,[BrandName_Chi]
      ,[ProductCatUID]
      ,[Deleted]
      ,[LastModifyDate]
      ,[LastModifyBy]
      ,[CreationDate]
      
   from [tblCust_Brand]


   --清除原有 Cust_Brand数据--
   USE [HKEA_CMS]
GO

/****** Object:  Table [dbo].[tblCust_Brand]    Script Date: 03/15/2011 18:04:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblCust_Brand](
	[CustBrandUID] [int] IDENTITY(1,1) NOT NULL,
	[BrandUID] [int] NULL,
	[ProductCatUID] [int] NULL,
	[Deleted] [int] NOT NULL,
	[LastModifyDate] [datetime] NOT NULL,
	[LastModifyBy] [varchar](50) NULL,
	[CreationDate] [datetime] NOT NULL,
 CONSTRAINT [PK_tblCust_Brand] PRIMARY KEY CLUSTERED 
(
	[CustBrandUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[tblCust_Brand] ADD  CONSTRAINT [DF_tblCust_Brand_Deleted]  DEFAULT ((0)) FOR [Deleted]
GO

ALTER TABLE [dbo].[tblCust_Brand] ADD  CONSTRAINT [DF_tblCust_Brand_LastModifyDate]  DEFAULT (getdate()) FOR [LastModifyDate]
GO

ALTER TABLE [dbo].[tblCust_Brand] ADD  CONSTRAINT [DF_tblCust_Brand_CreationDate]  DEFAULT (getdate()) FOR [CreationDate]
GO

--End---

alter table mtblActiviteType add IsFromHKEA int not null default(0)
alter table mtblActiviteType add code nvarchar(50)

insert into [mtblActiviteType] (activitetype_eng,activitetype_chi,deleted,lastmodifydate,lastmodifyby,creationdate,isfromhkea,code) values
('Luncheons / Receptions','Luncheons / Receptions',0,getdate(),'devteam',getdate(),1,'6.3')
insert into [mtblActiviteType] (activitetype_eng,activitetype_chi,deleted,lastmodifydate,lastmodifyby,creationdate,isfromhkea,code) values
('SR Programme','SR Programme',0,getdate(),'devteam',getdate(),1,'6.2')
insert into [mtblActiviteType] (activitetype_eng,activitetype_chi,deleted,lastmodifydate,lastmodifyby,creationdate,isfromhkea,code) values
('Seminars / Conferences','Seminars / Conferences',0,getdate(),'devteam',getdate(),1,'6.1')
insert into [mtblActiviteType] (activitetype_eng,activitetype_chi,deleted,lastmodifydate,lastmodifyby,creationdate,isfromhkea,code) values
('Fair / Delegation','Fair / Delegation',0,getdate(),'devteam',getdate(),1,'5.1')















--修改tblActivite Activite_Name(nvarchar(255))、Activite_Theme(nvarchar(255))